﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Time.DB
{
    public class SQLServerHelp : DbUtil
    {
        /// <summary>
        /// 数据循环批量插入处理，有主键约束的无法使用。
        /// </summary>
        /// <param name="TableName">目标表</param>
        /// <param name="dt">源数据</param>
        public void SqlCopyDt(string TableName, DataTable dt, string KeyCol = "ID")
        {
            string insertStr = "insert into " + TableName + "(";
            string valuesStr = " values(";
            List<string> sqlStr = new List<string>();
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                SqlParameter[] p = new SqlParameter[dt.Columns.Count];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Rows[r][i].ToString().Equals(KeyCol))
                    {
                        continue;
                    }
                    if (i == dt.Columns.Count - 1)
                    {
                        insertStr += dt.Columns[i].ColumnName + ")";
                        string paName = "@" + dt.Columns[i].ColumnName;
                        valuesStr += "@" + dt.Columns[i].ColumnName + ")";
                        p[i] = new SqlParameter(paName, dt.Rows[r][i].ToString());

                    }
                    else
                    {
                        valuesStr += "@" + dt.Columns[i].ColumnName + " ,";
                        insertStr += dt.Columns[i].ColumnName + ",";
                    }
                }
                insertStr += valuesStr;
                sqlStr.Add(insertStr);
            }
            ExecuteNonQueryTransaction(sqlStr);
        }
        public void TransferData(string sheetName = "sheet1")
        {
            DataTable tempDt = new DataTable();
            try
            {
                //获取全部数据
                string strExcel = "";
                strExcel = string.Format("select * from [{0}$]", sheetName);
                
                tempDt =  ExecuteDataTable(strExcel, null);

                //如果目标表不存在则创建
                string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
                foreach (System.Data.DataColumn c in tempDt.Columns)
                {
                    strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
                }
                strSql = strSql.Trim(',') + ")";
              
                ExecuteNonQuery(strSql, null);

            }
            catch (Exception ex)
            {

            }
        }
        /// <summary>
        /// 通过BCP快速转换数据仅限于微软的数据库(excel-access-sqlserver)
        /// </summary>
        /// <param name="sourceTable">需要导出或者导入的数据库表名</param>
        /// <param name="filePath">需要导出或者导入的文件名包含全目录</param>
        /// <param name="inputBl">是否导出(以数据库为方向，从数据导出为true)</param>
        public void ExBcpToSql(string sourceTable, string filePath, bool inputBl = true)
        {
            string strSql = null;
            string strInOrOut = "in";//导入和导出方向
            if (inputBl)
            {
                strInOrOut = "out";
            }
            //bcp db_name.dbo.T_tablename  out c:\a.txt - c - T 
            //bcp db_name.dbo.T_tablename  in c:\a.txt - c - T
            strSql = string.Format("bcp {0}.dbo.{1}  {2} {3} - c - T", objdbLink.DBName, sourceTable, strInOrOut, filePath);
            //strSql = string.Format(" exec master..xp_cmdshell '{0}'",strSql);

            // strSql = string.Format("bcp {0}.dbo.{1} out {2} - c - q - S '{3} , {4}'  - U '{5}' - P '{6}'", objdbLink.DBName, sourceTable, filePath);
            ExecuteNonQuery(strSql, null);
        }
        /// <summary>
        /// 远程操作BCP导入或导出
        /// </summary> 
        /// <param name="sourceTable">需要导出或者导入的数据库表名</param>
        /// <param name="filePath">需要导出或者导入的文件名包含全目录</param>
        /// <param name="DBIpAddress">远程数据库地址</param>
        /// <param name="iPort">端口</param>
        /// <param name="strUser">用户名</param>
        /// <param name="strPw">密码</param>
        /// <param name="inputBl">是否导出(以数据库为方向，从数据导出为true)</param>
        public void ExBcpToSql(string sourceTable, string filePath, string DBIpAddress, int iPort, string strUser, string strPw, bool inputBl = true)
        {
            string strSql = null;
            string strInOrOut = "in";//导入和导出方向
            if (inputBl)
            {
                strInOrOut = "out";
            }
            //bcp db_name.dbo.tablename out d:\table.txt - c - q - S"120.26.76.220,1833" - U"sa" - P"password"
            strSql = string.Format("bcp {0}.dbo.{1} {2} {3} - c - q - S '{4} , {5}'  - U '{6}' - P '{7}'", objdbLink.DBName, sourceTable, strInOrOut, filePath, DBIpAddress, iPort, strUser, strPw);
            ExecuteNonQuery(strSql, null);
        }
        /// <summary>
        /// 数据批量插入处理，有主键约束的无法使用。
        /// </summary>
        /// <param name="TableName">目标表</param>
        /// <param name="dt">源数据</param>
        /// <param name="KeyCol">目标数据主键</param>
        public static Boolean SqlBulkCopyByDatatable(string TableName, DataTable dt, string KeyCol = "ID")
        {
            Boolean bl = false;
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.UseInternalTransaction))
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = TableName;//目标表
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            if (dt.Columns[i].ColumnName.Equals(KeyCol))//目标数据的主键不做插入处理
                            {
                                continue;
                            }
                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                        }
                        sqlbulkcopy.BatchSize = 1000;//每次传输的行数
                        sqlbulkcopy.NotifyAfter = 1000;//进度提示的行数
                        sqlbulkcopy.BulkCopyTimeout = 0;//设定超时时间，默认为0，不限制
                        sqlbulkcopy.WriteToServer(dt);
                        bl = true;
                    }
                    catch (System.Exception ex)
                    {
                        bl = false;
                        // SqlCopyDt(TableName, dt, KeyCol);
                    }
                    finally
                    {
                        conn.Close();
                        conn.Dispose();
                    }

                }
            }
            return bl;
        }
        /// <summary>
        /// 启用SQLCopy快速实现表数据转移操作
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="dt"> 数据源DataTable </param> 
        /// <param name="DestFieldArr">目标数据表头</param>
        /// <returns></returns>
        public static Boolean SqlBulkCopyByDatatableMap(string TableName, DataTable dt, string[] DestFieldArr)
        {
            Boolean rtn = true;
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.KeepIdentity))//UseInternalTransaction
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = TableName;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            //sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, DestFieldArr[i]);
                        }
                        sqlbulkcopy.BatchSize = 1000;//每次传输的行数
                        sqlbulkcopy.NotifyAfter = 1000;//进度提示的行数
                        sqlbulkcopy.BulkCopyTimeout = 0;//设定超时时间，默认为0，不限制
                        sqlbulkcopy.WriteToServer(dt);
                        rtn = true;
                    }
                    catch (System.Exception ex)
                    {
                        rtn = false;
                    }
                    finally
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return rtn;
        }
        /// <summary>
        /// 启用SQLCopy快速实现表数据转移操作
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="dt"> 数据源DataTable </param> 
        /// <param name="SourceFieldArr">数据源表头</param>
        /// <param name="DestFieldArr">目标数据表头</param>
        /// <returns></returns>
        public static Boolean SqlBulkCopyByDatatableMap(string TableName, DataTable dt, string[] SourceFieldArr, string[] DestFieldArr, string KeyCol = "ID")
        {
            Boolean rtn = true;
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.KeepIdentity))//UseInternalTransaction
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = TableName;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            //sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                            sqlbulkcopy.ColumnMappings.Add(SourceFieldArr[i], DestFieldArr[i]);
                        }
                        sqlbulkcopy.BulkCopyTimeout = 0;
                        sqlbulkcopy.WriteToServer(dt);
                        rtn = true;
                    }
                    catch (System.Exception ex)
                    {
                        rtn = false;
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return rtn;
        }

    }
}
